﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataBase;
using Oracle.ManagedDataAccess.Client;

namespace DataBase
{

    /// <summary>
    /// Oracle管理类
    /// </summary>
    public class OracleManager : IDisposable
    {
        private OracleConnection cnn;
        private OracleCommand cmd;
        /// <summary>
        /// 保存表头,执行数据库命令后可用
        /// </summary>
        static public string[] tableHeader;

        #region 构造函数相关方法
        /// <summary>
        /// 根据参数创建数据库配置链接对象
        /// </summary>
        /// <param name="connecttionText">配置信息</param>
        public OracleManager(string connecttionText)
        {
            ConnecttionText = connecttionText;
        }


        /// <summary>
        /// 根据参数创建数据库配置链接对象
        /// </summary>
        /// <param name="dataBaseType">提供默认配置信息</param>
        public OracleManager(DataBaseType dataBaseType)
            : this(GetDefaultConnecttionText(dataBaseType))
        {
        }

        /// <summary>
        /// 根据参数创建数据库配置链接对象
        /// </summary>
        /// <param name="dataBaseName">配置名</param>
        /// <param name="user_id">用户名</param>
        /// <param name="password">密码</param>
        public OracleManager(string dataBaseName, string user_id, string password)
            : this(GetConnecttionText(dataBaseName, user_id, password))
        {
        }

        /// <summary>
        /// 根据用户提供信息,创建数据库连接字段
        /// </summary>
        /// <param name="dataBaseName">数据库配置名</param>
        /// <param name="userId">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>返回数据库链接字段</returns>
        internal static string GetConnecttionText(string dataBaseName, string userId, string password)
        {
            OracleConnectionStringBuilder ocb = new OracleConnectionStringBuilder();
            ocb.UserID = dataBaseName;
            ocb.Password = userId;
            ocb.DataSource = password;
            return ocb.ConnectionString;
        }

        /// <summary>
        /// 获取默认配置信息
        /// </summary>
        /// <param name="dataBaseType">默认配置信息</param>
        /// <returns>返回数据库链接字段</returns>
        internal static string GetDefaultConnecttionText(DataBaseType dataBaseType)
        {
            string t;
            switch (dataBaseType)
            {
                case DataBaseType.Oracle:
                    t = DataBase.Properties.Settings.Default.oracle_id;
                    break;
                case DataBaseType.SQL:
                    t = DataBase.Properties.Settings.Default.sql_id;
                    break;
                default:
                    t = DataBase.Properties.Settings.Default.oracle_id;
                    break;
            }
            return t;
        }

        /// <summary>
        /// 配置信息
        /// </summary>
        private string ConnecttionText
        {
            set;
            get;
        }


        /// <summary>
        /// 获取链接字符串
        /// </summary>
        private string CommandText
        {
            set;
            get;
        }
        #endregion


        #region 获取服务器对象
        /// <summary>
        /// 获取服务器Connection对象
        /// </summary>
        /// <returns></returns>
        public OracleConnection GetOracleConnection
        {

            get
            {
                cnn = new OracleConnection(ConnecttionText);
                return cnn;
            }
        }


        /// <summary>
        /// 获取OracleCommand对象
        /// </summary>
        public OracleCommand GetOracleCommand
        {
            get
            {
                cmd = new OracleCommand(this.CommandText, cnn);
                return cmd;
            }
        }

        #endregion


        #region 数据库查询方法
        /// <summary>
        /// 根据SQL语句查询
        /// </summary>
        /// <param name="sqlText">sql语句</param>
        /// <returns>返回可枚举object集合</returns>
        public IEnumerable<Batch> ExecuteTable(string sqlText)
        {
            CommandText = sqlText;
            using (cnn = GetOracleConnection)

            {
                Open(cnn);
                cmd = GetOracleCommand;
                Batch bt = new Batch();
                foreach (var v in this.ExecuteReader(cmd))
                {
                    bt.RowsList.Add(v);
                }
                yield return bt;
                this.Dispose();
            }

        }


        /// <summary>
        /// 执行没有返回结果的存储过程
        /// </summary>
        /// <param name="proceduresName">存储过程名称</param>
        /// <param name="values">存储过程参数</param>
        public void ExecuteNoneProcedurs(string proceduresName, params object[] values)
        {
            using (cnn = GetOracleConnection)
            {
                Open(cnn);
                cmd = GetOracleCommand;
                cmd = ParameterValueAdd(proceduresName, cmd, values);
                cmd.ExecuteNonQuery();
                this.Dispose();
            }
        }


        /// <summary>
        /// 存储过程查询,逐行输出
        /// </summary>
        /// <param name="proceduresName">存储过程名称</param>
        /// <param name="values">存储过程参数</param>
        /// <returns>存储过程参数</returns>
        private IEnumerable<object[]> ExecuteProcedursToObject(string proceduresName, params object[] values)
        {
            using (cnn = GetOracleConnection)
            {
                Open(cnn);
                OracleCommand cmd = GetOracleCommand;
                cmd = ParameterValueAdd(proceduresName, cmd, values);

                foreach (var v in this.ExecuteReader(cmd))
                {
                    yield return v;
                }
            }
        }


        /// <summary>
        /// 存储过程查询,按指定行数分批次输出
        /// </summary>
        /// <param name="proceduresName">存储过程名称</param>
        /// <param name="outputRows">存储过程参数</param>
        /// <param name="values"></param>
        /// <returns>返回批次List Object数组对象</returns>
        private IEnumerable<List<object[]>> ExecuteProcedursToObject(string proceduresName, int outputRows, params object[] values)
        {

            List<object[]> t = new List<object[]>();
            //从结果集中每次返回一条数据
            foreach (object[] item in ExecuteProcedursToObject(proceduresName, values))
            {
                //对每条数据添加到数据库仓库
                t.Add(item);
                if (t.Count >= outputRows)
                {
                    yield return t;
                    t = new List<object[]>();
                }
            }
            if (t.Count < outputRows)
            {
                yield return t;
            }
        }

        /// <summary>
        /// 根据行数分批次输出数据
        /// </summary>
        /// <param name="proceduresName">存储过程名</param>
        /// <param name="outputRows">每批次输出行数</param>
        /// <param name="values">存储过程参数</param>
        /// <returns>返回Batch批次对象</returns>
        public IEnumerable<Batch> ExecuteProcedursToBatch(string proceduresName, int outputRows, params object[] values)
        {

            Batch bc = new Batch();
            foreach (OracleRow item in ExecuteProcedursToRow(proceduresName, values))
            {
                bc.RowsList.Add(item.RowData);
                if (bc.RowsList.Count >= outputRows)
                {
                    yield return bc;
                    bc = new Batch();
                }
            }
            if (bc.RowsList.Count < outputRows)
            {
                yield return bc;
            }
        }

        /// <summary>
        /// 以枚举形式返回存储过程结果集中每条数据
        /// </summary>
        /// <param name="proceduresName">存储过程名称</param>
        /// <param name="values">存储参数</param>
        /// <returns></returns>
        public IEnumerable<OracleRow> ExecuteProcedursToRow(string proceduresName, params object[] values)
        {
            OracleRow mRow = new OracleRow();
            foreach (var item in ExecuteProcedursToObject(proceduresName, values))
            {
                mRow.RowData = item;
                yield return mRow;
            }
        }

        /// <summary>
        /// 检索数据库存储过程信息(如:参数名,参数类型)
        /// </summary>
        /// <param name="proceduresName"></param>
        /// <param name="cmd"></param>
        /// <returns></returns>
        private static OracleParameter[] OracleHelperParameterCache(string proceduresName, OracleCommand cmd)
        {

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = proceduresName;
            // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
            OracleCommandBuilder.DeriveParameters(cmd);

            // 如果不包含返回值参数,将参数集中的每一个参数删除.
            //cmd.Parameters.RemoveAt(0);

            // 创建参数数组
            OracleParameter[] discoveredParameters = new OracleParameter[cmd.Parameters.Count];


            // 将cmd的Parameters参数集复制到discoveredParameters数组.
            cmd.Parameters.CopyTo(discoveredParameters, 0);

            // 初始化参数值为 DBNull.Value.
            foreach (OracleParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        /// <summary>
        /// 根据存储过程添加值
        /// </summary>
        /// <param name="proceduresName">存储过程名</param>
        /// <param name="cmd">OracleCommand对象</param>
        /// <param name="values">Object数组类型参数</param>
        /// <returns>返回带有数据类型及值的OracleCommand对象</returns>
        private OracleCommand ParameterValueAdd(string proceduresName, OracleCommand cmd, object[] values)
        {
            OracleParameter[] parameter = OracleHelperParameterCache(proceduresName, cmd);
            int index = 0;
            foreach (var v in values)
            {
                parameter[index].Value = v;
                var dbtype = parameter[index].DbType;
                ParametersType(dbtype, v);
                index++;
            }
            return cmd;
        }


        /// <summary>
        /// 根据DataReader返回一行数据
        /// </summary>
        /// <param name="command">OracleCommand对象</param>
        /// <returns>每行数据</returns>
        internal IEnumerable<object[]> ExecuteReader(OracleCommand command)
        {
            OracleDataReader odr = command.ExecuteReader();
            DataReader = odr;
            // 保存表头信息
            GetTableHeader();
            while (odr.Read())
            {
                //todo这个new是不是放在while外面 每次更改一次行对象赋值就可以了?
                object[] obj = new object[odr.FieldCount];
                odr.GetValues(obj);
                yield return obj;
            }
        }
        #endregion

        /// <summary>
        /// 获取表头信息
        /// </summary>
        internal void GetTableHeader()
        {
            tableHeader = new string[DataReader.FieldCount];
            for (int i = 0; i < DataReader.FieldCount; i++)
            {
                tableHeader[i] = DataReader.GetName(i);
            }
        }

        /// <summary>
        /// 保存DataReader信息
        /// </summary>
        private OracleDataReader DataReader
        {
            get;
            set;
        }

        #region 数据库执行命令
        /// <summary>
        /// 打开数据库查询语句
        /// </summary>
        /// <param name="connection">数据库对象</param>
        internal void Open(OracleConnection connection)
        {

            if (ConnectionSate(connection, System.Data.ConnectionState.Open) != true)
            {
                connection.Open();
            }
        }


        /// <summary>
        /// 判断用户传递与存储过程参数类型是否一致
        /// </summary>
        /// <param name="dbtype">存储过程参数类型</param>
        /// <param name="parameterValue">用户传递参数类型</param>
        private void ParametersType(System.Data.DbType dbtype, object parameterValue)
        {
            try
            {
                if (dbtype.GetType() == parameterValue.GetType())
                {
                    throw new Exception(string.Format("存储过程参数类型为:{0},实际传入类型为{1},请检查", dbtype.GetType(), parameterValue.GetType()));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }



        /// <summary>
        /// 判断数据库当前状态
        /// </summary>
        /// <param name="connection">数据库connection对象</param>
        /// <param name="sate">System.Data.ConnectionState当前状态</param>
        /// <returns>返回布尔值</returns>
        /// 
        private bool ConnectionSate(OracleConnection connection, System.Data.ConnectionState sate)
        {
            return connection.State == sate;
        }

        /// <summary>
        /// 释放数据库System.ComponentModel.Container资源
        /// </summary>
        public void Dispose()
        {
            if (ConnectionSate(cnn, System.Data.ConnectionState.Closed) != true)
            {
                cnn.Close();
            }

            cnn.Dispose();
        }
        #endregion
    }

}
